# dashboard.py
import sqlite3
import pandas as pd
import matplotlib.pyplot as plt
import seaborn as sns
import numpy as np
from matplotlib.gridspec import GridSpec, GridSpecFromSubplotSpec

# 设置中文显示
plt.rcParams['font.sans-serif'] = ['SimHei']  # 用黑体显示中文
plt.rcParams['axes.unicode_minus'] = False  # 正常显示负号


def create_dashboard(conn, shop_id=1):
    """创建零食店铺流量分析看板"""
    # 获取店铺名称
    shop_name = pd.read_sql(f'SELECT shop_name FROM shops WHERE shop_id = {shop_id}', conn).iloc[0, 0]

    # 设置颜色主题
    colors = ['#FF6B6B', '#4ECDC4', '#FFA62B', '#1A535C', '#F7B801', '#1E91D6']
    sns.set_palette(sns.color_palette(colors))

    # 创建大图
    plt.figure(figsize=(18, 22), dpi=100)
    plt.suptitle(f'{shop_name} - 零食店铺流量分析看板', fontsize=22, fontweight='bold', color='#1A535C')

    # 使用GridSpec创建复杂布局
    gs = GridSpec(5, 3, height_ratios=[1.2, 1, 1, 1, 1.2], width_ratios=[1, 1, 1])

    # 1. 流量总览趋势图
    ax1 = plt.subplot(gs[0, :])
    traffic_df = pd.read_sql(f'''
        SELECT date, total_visitors, conversion_rate, payment_amount 
        FROM traffic_overview 
        WHERE shop_id = {shop_id} 
        ORDER BY date
    ''', conn)
    traffic_df['date'] = pd.to_datetime(traffic_df['date'])

    # 计算7天移动平均
    traffic_df['visitors_7d'] = traffic_df['total_visitors'].rolling(window=7).mean()

    ax1.plot(traffic_df['date'], traffic_df['total_visitors'],
             label='日访客数', marker='o', linewidth=2, alpha=0.7)
    ax1.plot(traffic_df['date'], traffic_df['visitors_7d'],
             label='7日平均访客', linewidth=3, color='#FF6B6B')
    ax1.set_ylabel('访客数', fontsize=12)

    ax2 = ax1.twinx()
    ax2.plot(traffic_df['date'], traffic_df['conversion_rate'] * 100,
             color='#4ECDC4', label='转化率', marker='s', linestyle='--', linewidth=2)
    ax2.set_ylabel('转化率 (%)', color='#4ECDC4', fontsize=12)
    ax2.tick_params(axis='y', labelcolor='#4ECDC4')

    ax3 = ax1.twinx()
    ax3.spines['right'].set_position(('outward', 60))
    ax3.plot(traffic_df['date'], traffic_df['payment_amount'],
             color='#FFA62B', label='支付金额', marker='^', linestyle='-.', linewidth=2)
    ax3.set_ylabel('支付金额 (元)', color='#FFA62B', fontsize=12)
    ax3.tick_params(axis='y', labelcolor='#FFA62B')

    ax1.set_title('流量总览趋势 (最近90天)', fontsize=16, pad=15, color='#1A535C')
    ax1.grid(True, linestyle='--', alpha=0.7)
    ax1.legend(loc='upper left', frameon=True, framealpha=0.9)
    ax2.legend(loc='upper center', frameon=True, framealpha=0.9)
    ax3.legend(loc='upper right', frameon=True, framealpha=0.9)

    # 2. 流量来源分布
    ax4 = plt.subplot(gs[1, 0])
    sources_df = pd.read_sql(f'''
        SELECT source_type, SUM(visitors) as total_visitors 
        FROM traffic_sources 
        WHERE shop_id = {shop_id}
        GROUP BY source_type
    ''', conn)

    wedges, texts, autotexts = ax4.pie(
        sources_df['total_visitors'],
        labels=sources_df['source_type'],
        autopct='%1.1f%%',
        startangle=90,
        wedgeprops={'edgecolor': 'w', 'linewidth': 1.5},
        textprops={'fontsize': 10},
        colors=colors[:4]
    )
    ax4.set_title('流量来源分布', fontsize=14, color='#1A535C')

    # 3. 零食关键词TOP10
    ax5 = plt.subplot(gs[1, 1])
    keywords_df = pd.read_sql(f'''
        SELECT keyword, SUM(visitors) as total_visitors 
        FROM keywords 
        WHERE shop_id = {shop_id} AND rank <= 10
        GROUP BY keyword 
        ORDER BY total_visitors DESC 
        LIMIT 10
    ''', conn)

    sns.barplot(x='total_visitors', y='keyword', data=keywords_df,
                ax=ax5, palette='viridis')
    ax5.set_title('零食关键词TOP10', fontsize=14, color='#1A535C')
    ax5.set_xlabel('访客数')
    ax5.set_ylabel('')
    ax5.grid(axis='x', linestyle='--', alpha=0.7)

    # 4. 热销零食TOP5
    ax6 = plt.subplot(gs[1, 2])
    products_df = pd.read_sql(f'''
        SELECT product_name, visitors, conversion_rate 
        FROM product_traffic 
        WHERE shop_id = {shop_id} AND date = (
            SELECT MAX(date) FROM product_traffic WHERE shop_id = {shop_id}
        ) AND rank <= 5
    ''', conn)

    # 简化商品名称显示
    products_df['short_name'] = products_df['product_name'].apply(
        lambda x: x.split('(')[0][:10] + '...' if len(x.split('(')[0]) > 10 else x.split('(')[0])

    sns.barplot(x='visitors', y='short_name', data=products_df,
                ax=ax6, palette='rocket')

    # 添加转化率标签
    for i, (rate, full_name) in enumerate(zip(products_df['conversion_rate'], products_df['product_name'])):
        ax6.text(products_df['visitors'].max() * 0.05, i,
                 f'{rate * 100:.1f}%', va='center', fontsize=10,
                 bbox=dict(facecolor='white', alpha=0.8, edgecolor='none'))

        # 添加完整商品名称作为注释
        ax6.annotate(full_name,
                     xy=(0, i),
                     xytext=(-100, 0),
                     textcoords='offset points',
                     ha='right', va='center',
                     fontsize=9,
                     color='#555555',
                     arrowprops=dict(arrowstyle='-', color='#888888'))

    ax6.set_title('热销零食TOP5 (带转化率)', fontsize=14, color='#1A535C')
    ax6.set_xlabel('访客数')
    ax6.set_ylabel('')
    ax6.grid(axis='x', linestyle='--', alpha=0.7)

    # 5. 人群特征分析 - 性别和年龄
    ax7 = plt.subplot(gs[2, :])
    demo_df = pd.read_sql(f'''
        SELECT crowd_type, gender, age_group, city, taoqi_value, proportion 
        FROM user_demographics 
        WHERE shop_id = {shop_id} AND date = (
            SELECT MAX(date) FROM user_demographics WHERE shop_id = {shop_id}
        )
    ''', conn)

    # 性别分布
    gender_df = demo_df[demo_df['gender'].notnull()]
    gender_pivot = gender_df.pivot_table(index='crowd_type', columns='gender',
                                         values='proportion', aggfunc='sum').reset_index()

    # 年龄分布
    age_df = demo_df[demo_df['age_group'].notnull()]
    age_order = ['18-24岁', '25-29岁', '30-34岁', '35-39岁', '40-49岁', '50岁以上']
    age_df['age_group'] = pd.Categorical(age_df['age_group'], categories=age_order, ordered=True)
    age_pivot = age_df.pivot_table(index='crowd_type', columns='age_group',
                                   values='proportion', aggfunc='sum').reset_index()

    # 创建子图
    inner_gs = GridSpecFromSubplotSpec(1, 2, subplot_spec=gs[2, :], width_ratios=[1, 2])
    ax7_1 = plt.subplot(inner_gs[0])
    ax7_2 = plt.subplot(inner_gs[1])

    # 绘制性别分布
    gender_pivot.plot(x='crowd_type', kind='bar', stacked=True,
                      ax=ax7_1, width=0.6, color=['#4ECDC4', '#FF6B6B'])
    ax7_1.set_title('性别分布', fontsize=12)
    ax7_1.set_ylabel('比例')
    ax7_1.set_xlabel('人群类型')
    ax7_1.legend(title='性别')
    ax7_1.grid(axis='y', linestyle='--', alpha=0.7)

    # 绘制年龄分布
    age_pivot.plot(x='crowd_type', kind='bar', stacked=True,
                   ax=ax7_2, width=0.6, colormap='viridis')
    ax7_2.set_title('年龄分布', fontsize=12)
    ax7_2.set_ylabel('比例')
    ax7_2.set_xlabel('人群类型')
    ax7_2.legend(title='年龄组', bbox_to_anchor=(1.05, 1), loc='upper left')
    ax7_2.grid(axis='y', linestyle='--', alpha=0.7)

    ax7 = plt.subplot(gs[2, :])
    ax7.axis('off')  # 隐藏主图框
    ax7.set_title('零食消费者人群特征', fontsize=16, color='#1A535C', pad=20)

    # 6. 城市分布和淘气值分布
    ax8 = plt.subplot(gs[3, :])

    # 城市分布
    city_df = demo_df[demo_df['city'].notnull()]
    city_top10 = city_df.groupby('city')['proportion'].sum().nlargest(10).reset_index()

    # 淘气值分布
    taoqi_df = demo_df[demo_df['taoqi_value'].notnull()]
    taoqi_df = taoqi_df.groupby('taoqi_value')['proportion'].sum().reset_index()

    # 创建子图
    inner_gs2 = GridSpecFromSubplotSpec(1, 2, subplot_spec=gs[3, :])
    ax8_1 = plt.subplot(inner_gs2[0])
    ax8_2 = plt.subplot(inner_gs2[1])

    # 城市分布条形图
    sns.barplot(x='proportion', y='city', data=city_top10,
                ax=ax8_1, palette='coolwarm')
    ax8_1.set_title('城市分布TOP10', fontsize=12)
    ax8_1.set_xlabel('比例')
    ax8_1.set_ylabel('')
    ax8_1.grid(axis='x', linestyle='--', alpha=0.7)

    # 淘气值分布条形图
    sns.barplot(x='taoqi_value', y='proportion', data=taoqi_df,
                ax=ax8_2, palette='mako')
    ax8_2.set_title('淘气值分布', fontsize=12)
    ax8_2.set_xlabel('淘气值区间')
    ax8_2.set_ylabel('比例')
    ax8_2.grid(axis='y', linestyle='--', alpha=0.7)
    ax8_2.set_xticklabels(taoqi_df['taoqi_value'], rotation=45)

    ax8 = plt.subplot(gs[3, :])
    ax8.axis('off')  # 隐藏主图框
    ax8.set_title('地域与消费能力分析', fontsize=16, color='#1A535C', pad=20)

    # 7. 详细数据表格
    ax9 = plt.subplot(gs[4, :])
    latest_date = traffic_df['date'].max().strftime('%Y-%m-%d')

    # 获取最新数据
    metrics_df = pd.read_sql(f'''
        SELECT 
            total_visitors,
            total_pageviews,
            bounce_rate,
            conversion_rate,
            payment_count,
            payment_amount
        FROM traffic_overview
        WHERE shop_id = {shop_id} AND date = '{latest_date}'
    ''', conn)

    # 创建表格数据
    table_data = [
        ['总访客数', f"{metrics_df['total_visitors'].values[0]:,}"],
        ['总浏览量', f"{metrics_df['total_pageviews'].values[0]:,}"],
        ['跳失率', f"{metrics_df['bounce_rate'].values[0] * 100:.1f}%"],
        ['转化率', f"{metrics_df['conversion_rate'].values[0] * 100:.1f}%"],
        ['支付人数', f"{metrics_df['payment_count'].values[0]:,}"],
        ['支付金额', f"¥{metrics_df['payment_amount'].values[0]:,.2f}"]
    ]

    # 创建表格
    table = ax9.table(
        cellText=table_data,
        colLabels=['指标', '值'],
        cellLoc='center',
        loc='center',
        colColours=['#1A535C', '#4ECDC4'],
        cellColours=[['#F7FFF7', '#F7FFF7'] for _ in range(len(table_data))]
    )

    # 样式设置
    table.auto_set_font_size(False)
    table.set_fontsize(12)
    table.scale(1, 2)
    ax9.axis('off')
    ax9.set_title(f'关键指标概览 ({latest_date})', fontsize=16, color='#1A535C', pad=20)

    # 添加整体说明
    plt.figtext(0.5, 0.01,
                '数据来源：淘宝天猫零食店铺流量看板 | 注：所有数据均为模拟数据，仅用于演示目的',
                ha='center', fontsize=10, color='#555555')

    # 调整布局
    plt.tight_layout(rect=[0, 0.02, 1, 0.97])
    plt.subplots_adjust(hspace=0.4, wspace=0.3)

    # 保存看板
    plt.savefig(f'snack_shop_dashboard_{shop_id}.png', bbox_inches='tight')
    plt.show()
    print(f"零食店铺看板已保存为 'snack_shop_dashboard_{shop_id}.png'")

